﻿/*
 ******************************************************************
 *      
 * 域名空间　	: GreatB2C.DALSQLServer
 * 类　名　称	: Client.cs
 * 类　功　能	: 数据操作实现层
 * 类　流　程    :                             
 * 运用算法　	:                             
 * 编　写　人	: WHL             
 * 编写日期　	: 2011-3-28
 * 最近更新日期　:
 * 版　　本　	: V1.0                           
 * 描　　述　	: Descript of Class                                                 
 *                                        
 ******************************************************************
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using GreatB2C.Model;
using GreatB2C.DALFactory;
using GreatB2C.DALProfile;
using GreatB2C.Config;
using GreatB2C.Utility;

namespace GreatB2C.DALSQLServer
{
    /// <summary>
    /// 类描述：
    /// </summary>
    [Serializable]
    public class Client : DbBase, IClient
    {
        #region implement methods

        /// <summary>
        /// 获取参数参数列表
        /// </summary>
        /// <param name="ac"></param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(GreatB2C.Model.Client ac)
        {
            int i = 0;
            SqlParameter[] param = new SqlParameter[0];

            param[i] = new SqlParameter("@ClientID", SqlDbType.Int, 4);
            param[i++].Value = ac.ClientID;

            param[i] = new SqlParameter("@ParentID", SqlDbType.Int, 4);
            param[i++].Value = ac.ParentID;

            param[i] = new SqlParameter("@ClientNum", SqlDbType.NVarChar, 20);
            param[i++].Value = ac.ClientNum;

            param[i] = new SqlParameter("@ClientType", SqlDbType.Int, 4);
            param[i++].Value = ac.ClientType;

            param[i] = new SqlParameter("@ClientName", SqlDbType.NVarChar, 100);
            param[i++].Value = ac.ClientName;

            param[i] = new SqlParameter("@ShortedForm", SqlDbType.NVarChar, 20);
            param[i++].Value = ac.ShortedForm;

            param[i] = new SqlParameter("@Area", SqlDbType.Int, 4);
            param[i++].Value = ac.Area;

            param[i] = new SqlParameter("@ClientField", SqlDbType.Int, 4);
            param[i++].Value = ac.ClientField;

            param[i] = new SqlParameter("@ValueLevel", SqlDbType.Int, 4);
            param[i++].Value = ac.ValueLevel;

            param[i] = new SqlParameter("@CreditLevel", SqlDbType.Int, 4);
            param[i++].Value = ac.CreditLevel;

            param[i] = new SqlParameter("@Importance", SqlDbType.Int, 4);
            param[i++].Value = ac.Importance;

            param[i] = new SqlParameter("@ConnectionLevel", SqlDbType.Int, 4);
            param[i++].Value = ac.ConnectionLevel;

            param[i] = new SqlParameter("@GroupID", SqlDbType.Int, 4);
            param[i++].Value = ac.GroupID;

            param[i] = new SqlParameter("@SourceType", SqlDbType.Int, 4);
            param[i++].Value = ac.SourceType;

            param[i] = new SqlParameter("@PhaseType", SqlDbType.Int, 4);
            param[i++].Value = ac.PhaseType;

            param[i] = new SqlParameter("@Remark", SqlDbType.NVarChar, 16);
            param[i++].Value = ac.Remark;

            param[i] = new SqlParameter("@VisitTimes", SqlDbType.Int, 4);
            param[i++].Value = ac.VisitTimes;

            param[i] = new SqlParameter("@ServiceTimes", SqlDbType.Int, 4);
            param[i++].Value = ac.ServiceTimes;

            param[i] = new SqlParameter("@ComplainTimes", SqlDbType.Int, 4);
            param[i++].Value = ac.ComplainTimes;

            param[i] = new SqlParameter("@LastVisitTime", SqlDbType.DateTime, 8);
            param[i++].Value = ac.LastVisitTime;

            param[i] = new SqlParameter("@LastServiceTime", SqlDbType.DateTime, 8);
            param[i++].Value = ac.LastServiceTime;

            param[i] = new SqlParameter("@LastComplainTime", SqlDbType.DateTime, 8);
            param[i++].Value = ac.LastComplainTime;

            param[i] = new SqlParameter("@CreateTime", SqlDbType.DateTime, 8);
            param[i++].Value = ac.CreateTime;

            param[i] = new SqlParameter("@UpdateTime", SqlDbType.DateTime, 8);
            param[i++].Value = ac.UpdateTime;

            param[i] = new SqlParameter("@Owner", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Owner;

            param[i] = new SqlParameter("@Balance", SqlDbType.Money, 8);
            param[i++].Value = ac.Balance;

            param[i] = new SqlParameter("@LastContactedTime", SqlDbType.DateTime, 8);
            param[i++].Value = ac.LastContactedTime;

            param[i] = new SqlParameter("@OldOwner", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.OldOwner;

            param[i] = new SqlParameter("@IsBusiness", SqlDbType.Bit, 1);
            param[i++].Value = ac.IsBusiness;


            return param;
        }

        /// <summary>
        /// 检查是否有重复记录。
        /// 注意：这里要手动修改一下，需要加入次主键做为第二条件（主要是为了修改操作）
        /// </summary>
        /// <param name="objModel">对应的数据映射类</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool IsExists(GreatB2C.Model.Client objModel)
        {
            string Sql = "SELECT Count(*) "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Client]"
                            + " WHERE "
                            + "	[ClientID] = @ClientID"
                //+" and 次主键=@次主键"
                            ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int count = Convert.ToInt32(DbHelper.ExecuteScalar(CommandType.Text, Sql, param).ToString());
                if (count > 0)
                {
                    return true;
                }

                return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 检索出表中所有记录	
        /// </summary>
        /// <param name="aryResult">返回对象数据集合</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public DataTable ListClient()
        {
            DataTable dtData = new DataTable();
            string Sql = "SELECT "
                            + "[ClientID],"
                            + "[ParentID],"
                            + "[ClientNum],"
                            + "[ClientType],"
                            + "[ClientName],"
                            + "[ShortedForm],"
                            + "[Area],"
                            + "[ClientField],"
                            + "[ValueLevel],"
                            + "[CreditLevel],"
                            + "[Importance],"
                            + "[ConnectionLevel],"
                            + "[GroupID],"
                            + "[SourceType],"
                            + "[PhaseType],"
                            + "[Remark],"
                            + "[VisitTimes],"
                            + "[ServiceTimes],"
                            + "[ComplainTimes],"
                            + "[LastVisitTime],"
                            + "[LastServiceTime],"
                            + "[LastComplainTime],"
                            + "[CreateTime],"
                            + "[UpdateTime],"
                            + "[Owner],"
                            + "[Balance],"
                            + "[LastContactedTime],"
                            + "[OldOwner],"
                            + "[IsBusiness]"
                            + " FROM "
                            + "[dbo].[" + Prefix + "Client]";

            try
            {
                return DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据表中主键ID查询记录信息
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <param name="DesModel">输出的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public GreatB2C.Model.Client ListByID(GreatB2C.Model.Client objModel)
        {
            string Sql = "SELECT * "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Client]"
                            + " WHERE "
                            + "	[ClientID] = @ClientID";

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                using (DataTable dtData = DbHelper.ExecuteTable(CommandType.Text, Sql, param))
                {
                    if (dtData.Rows.Count > 0)
                    {
                        GreatB2C.Model.Client modClass = new GreatB2C.Model.Client();

                        modClass.ClientID = Convert.ToInt32(dtData.Rows[0]["ClientID"].ToString());
                        modClass.ParentID = Convert.ToInt32(dtData.Rows[0]["ParentID"].ToString());
                        modClass.ClientNum = dtData.Rows[0]["ClientNum"].ToString();
                        modClass.ClientType = Convert.ToInt32(dtData.Rows[0]["ClientType"].ToString());
                        modClass.ClientName = dtData.Rows[0]["ClientName"].ToString();
                        modClass.ShortedForm = dtData.Rows[0]["ShortedForm"].ToString();
                        modClass.Area = Convert.ToInt32(dtData.Rows[0]["Area"].ToString());
                        modClass.ClientField = Convert.ToInt32(dtData.Rows[0]["ClientField"].ToString());
                        modClass.ValueLevel = Convert.ToInt32(dtData.Rows[0]["ValueLevel"].ToString());
                        modClass.CreditLevel = Convert.ToInt32(dtData.Rows[0]["CreditLevel"].ToString());
                        modClass.Importance = Convert.ToInt32(dtData.Rows[0]["Importance"].ToString());
                        modClass.ConnectionLevel = Convert.ToInt32(dtData.Rows[0]["ConnectionLevel"].ToString());
                        modClass.GroupID = Convert.ToInt32(dtData.Rows[0]["GroupID"].ToString());
                        modClass.SourceType = Convert.ToInt32(dtData.Rows[0]["SourceType"].ToString());
                        modClass.PhaseType = Convert.ToInt32(dtData.Rows[0]["PhaseType"].ToString());
                        modClass.Remark = dtData.Rows[0]["Remark"].ToString();
                        modClass.VisitTimes = Convert.ToInt32(dtData.Rows[0]["VisitTimes"].ToString());
                        modClass.ServiceTimes = Convert.ToInt32(dtData.Rows[0]["ServiceTimes"].ToString());
                        modClass.ComplainTimes = Convert.ToInt32(dtData.Rows[0]["ComplainTimes"].ToString());
                        modClass.LastVisitTime = Convert.ToDateTime(dtData.Rows[0]["LastVisitTime"].ToString());
                        modClass.LastServiceTime = Convert.ToDateTime(dtData.Rows[0]["LastServiceTime"].ToString());
                        modClass.LastComplainTime = Convert.ToDateTime(dtData.Rows[0]["LastComplainTime"].ToString());
                        modClass.CreateTime = Convert.ToDateTime(dtData.Rows[0]["CreateTime"].ToString());
                        modClass.UpdateTime = Convert.ToDateTime(dtData.Rows[0]["UpdateTime"].ToString());
                        modClass.Owner = dtData.Rows[0]["Owner"].ToString();
                        modClass.Balance = Decimal.Parse(dtData.Rows[0]["Balance"].ToString());
                        modClass.LastContactedTime = Convert.ToDateTime(dtData.Rows[0]["LastContactedTime"].ToString());
                        modClass.OldOwner = dtData.Rows[0]["OldOwner"].ToString();
                        modClass.IsBusiness = bool.Parse(dtData.Rows[0]["IsBusiness"].ToString());

                        return modClass;
                    }
                }

                return null;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 新增一条新记录到表中
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Insert(GreatB2C.Model.Client objModel)
        {
            string Sql = "INSERT INTO "
                        + "[dbo].[" + Prefix + "Client]"
                        + "("
                        + "[ParentID],"
                        + "[ClientNum],"
                        + "[ClientType],"
                        + "[ClientName],"
                        + "[ShortedForm],"
                        + "[Area],"
                        + "[ClientField],"
                        + "[ValueLevel],"
                        + "[CreditLevel],"
                        + "[Importance],"
                        + "[ConnectionLevel],"
                        + "[GroupID],"
                        + "[SourceType],"
                        + "[PhaseType],"
                        + "[Remark],"
                        + "[VisitTimes],"
                        + "[ServiceTimes],"
                        + "[ComplainTimes],"
                        + "[LastVisitTime],"
                        + "[LastServiceTime],"
                        + "[LastComplainTime],"
                        + "[CreateTime],"
                        + "[UpdateTime],"
                        + "[Owner],"
                        + "[Balance],"
                        + "[LastContactedTime],"
                        + "[OldOwner],"
                            + "[IsBusiness]"
                        + ") VALUES ("
                        + "@ParentID,"
                        + "@ClientNum,"
                        + "@ClientType,"
                        + "@ClientName,"
                        + "@ShortedForm,"
                        + "@Area,"
                        + "@ClientField,"
                        + "@ValueLevel,"
                        + "@CreditLevel,"
                        + "@Importance,"
                        + "@ConnectionLevel,"
                        + "@GroupID,"
                        + "@SourceType,"
                        + "@PhaseType,"
                        + "@Remark,"
                        + "@VisitTimes,"
                        + "@ServiceTimes,"
                        + "@ComplainTimes,"
                        + "@LastVisitTime,"
                        + "@LastServiceTime,"
                        + "@LastComplainTime,"
                        + "@CreateTime,"
                        + "@UpdateTime,"
                        + "@Owner,"
                        + "@Balance,"
                        + "@LastContactedTime,"
                        + "@OldOwner,"
                            + "@IsBusiness"
                        + ")";

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据主键ID更新指定记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Update(GreatB2C.Model.Client objModel)
        {
            string Sql = "UPDATE "
                        + "[dbo].[" + Prefix + "Client]"
                        + " SET "
                        + "[ParentID] = @ParentID,"
                        + "[ClientNum] = @ClientNum,"
                        + "[ClientType] = @ClientType,"
                        + "[ClientName] = @ClientName,"
                        + "[ShortedForm] = @ShortedForm,"
                        + "[Area] = @Area,"
                        + "[ClientField] = @ClientField,"
                        + "[ValueLevel] = @ValueLevel,"
                        + "[CreditLevel] = @CreditLevel,"
                        + "[Importance] = @Importance,"
                        + "[ConnectionLevel] = @ConnectionLevel,"
                        + "[GroupID] = @GroupID,"
                        + "[SourceType] = @SourceType,"
                        + "[PhaseType] = @PhaseType,"
                        + "[Remark] = @Remark,"
                        + "[VisitTimes] = @VisitTimes,"
                        + "[ServiceTimes] = @ServiceTimes,"
                        + "[ComplainTimes] = @ComplainTimes,"
                        + "[LastVisitTime] = @LastVisitTime,"
                        + "[LastServiceTime] = @LastServiceTime,"
                        + "[LastComplainTime] = @LastComplainTime,"
                        + "[CreateTime] = @CreateTime,"
                        + "[UpdateTime] = @UpdateTime,"
                        + "[Owner] = @Owner,"
                        + "[Balance] = @Balance,"
                        + "[LastContactedTime] = @LastContactedTime,"
                        + "[OldOwner] = @OldOwner,"
                            + "[IsBusiness] = @IsBusiness"
                        + " WHERE "
                        + "[ClientID] = @ClientID"
                        ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据主键删除指定记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Delete(GreatB2C.Model.Client objModel)
        {
            string Sql = "DELETE FROM "
                            + "[dbo].[" + Prefix + "Client]"
                            + " WHERE "
                            + "[ClientID] = @ClientID"
                            ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据ID列表批量删除记录
        /// 数据格式：1,2,3,4,5
        /// </summary>
        /// <param name="strList">ID列表</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool DeleteList(string strList)
        {
            string Sql = "DELETE FROM "
                            + "[dbo].[" + Prefix + "Client]"
                            + " WHERE "
                            + "[ClientID] in ('" + strList + "')"
                            ;

            try
            {
                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        #endregion

        #region self-define methods
        //public IList<GreatB2C.Model.Client> ListClient()
        //{
        //    return null;
        //}
        #endregion
    }
}
